﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Linq;
using System.Threading.Tasks;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using DevExpress.XtraEditors.Controls;
using System.Xml;
using System.Data.SqlClient;
using CDMS.Common;
using DevExpress.XtraEditors.Repository;
using System.Data.Sql;
using Microsoft.Win32;
using CDMS.Utilities;

namespace CDMS
{
    public partial class frmConnection : DevExpress.XtraEditors.XtraForm
    {
        string serverParameters = "SQLParameters.xml";
        bool isload = true;
        public frmConnection()
        {
            InitializeComponent();
            this.DialogResult = DialogResult.Cancel;
            textEditUsername.Enabled = false;
            textEditPassword.Enabled = false;
            LoadServerSettings();

            LoadAuthen();

            if (!System.IO.File.Exists(serverParameters)) return;
            XmlDocument doc = new XmlDocument();
            try
            {
                doc.Load(serverParameters);
            }
            catch { }
            if (doc.DocumentElement.Name == "Parameters")
            {
                string[] prm = doc.DocumentElement.InnerText.Split(new char[] { ';' });

                if (prm != null)
                {
                    if (prm.Length > 0)
                        lookUpEditServer.EditValue = prm[0];
                    if (prm.Length > 1)
                    {
                        List<Objects> list = new List<Objects>();
                        list.Add(new Objects() { Name = prm[1] });
                        var lup = lookUpEditDatabase;
                        lup.Properties.DataSource = list;
                        lup.Properties.DisplayMember = "Name";
                        lup.Properties.ValueMember = "Name";
                        DevExpress.XtraEditors.Controls.LookUpColumnInfoCollection coll = lup.Properties.Columns;
                        coll.Add(new DevExpress.XtraEditors.Controls.LookUpColumnInfo("Name", 0, "Name"));

                        lup.Properties.DropDownRows = list.Count <= 7 ? 7 : 20;
                        lup.Properties.BestFitMode = BestFitMode.BestFitResizePopup;
                        lup.Properties.SearchMode = SearchMode.AutoComplete;
                        lup.Properties.AutoSearchColumnIndex = 1;
                        lup.Properties.ShowHeader = false;
                        lup.Properties.Buttons.Clear();
                        lup.Properties.Buttons.AddRange(new DevExpress.XtraEditors.Controls.EditorButton[] {
            new DevExpress.XtraEditors.Controls.EditorButton(DevExpress.XtraEditors.Controls.ButtonPredefines.Combo),
            new DevExpress.XtraEditors.Controls.EditorButton(DevExpress.XtraEditors.Controls.ButtonPredefines.Close)});
                        lup.ButtonClick += lup_ButtonClick;
                        lookUpEditDatabase.EditValue = prm[1];
                    }
                    if (prm.Length > 2)
                        textEditUsername.Text = prm[2];
                    if (prm.Length > 3)
                        textEditPassword.Text = prm[3];

                    if (string.IsNullOrEmpty(textEditUsername.Text))
                        //lookUpEditAuthen.ItemIndex = 0;
                        //lookUpEditAuthen.EditValue = lookUpEditAuthen.Properties.GetDataSourceValue("Name", 0); 
                        //lookUpEditAuthen.EditValue = (lookUpEditAuthen.Properties.DataSource as List<Objects>)[0];
                        lookUpEditAuthen.EditValue = "Tài khoản hệ thống";
                }
            }

            lookUpEditDatabase.Properties.ShowHeader = false;
            
        }


        void LoadServerSettings()
        {
            string[] Arg = Properties.Settings.Default.ServerLog.Split(',');
            List<Objects> list = new List<Objects>();
            Array.Sort<string>(Arg);
            for (int i = 0; i < Arg.Length; i++)
            {
                if (Arg[i].Trim().Length > 0)
                {
                    list.Add(new Objects(){Name=Arg[i].ToString()});
                }
            }
            var lup = lookUpEditServer;
            lup.Properties.DataSource = list;
            lup.Properties.DisplayMember = "Name";
            lup.Properties.ValueMember = "Name";
            // Thêm thông tin cột
            DevExpress.XtraEditors.Controls.LookUpColumnInfoCollection coll = lup.Properties.Columns;
            coll.Add(new DevExpress.XtraEditors.Controls.LookUpColumnInfo("Name", 0, "Name"));

            lup.Properties.DropDownRows = list.Count <= 7 ? 7 : 20;
            lup.Properties.BestFitMode = BestFitMode.BestFitResizePopup;
            lup.Properties.SearchMode = SearchMode.AutoComplete;
            lup.Properties.AutoSearchColumnIndex = 1;
            lup.Properties.ShowHeader = false;
            lup.Properties.Buttons.Clear();
            lup.Properties.Buttons.AddRange(new DevExpress.XtraEditors.Controls.EditorButton[] {
            new DevExpress.XtraEditors.Controls.EditorButton(DevExpress.XtraEditors.Controls.ButtonPredefines.Combo),
            new DevExpress.XtraEditors.Controls.EditorButton(DevExpress.XtraEditors.Controls.ButtonPredefines.Close)});
            lup.ButtonClick += lup_ButtonClick;
        }

        void LoadAuthen()
        {
            List<Objects> list = new List<Objects>();
            list.Add(new Objects() { Name = "Tài khoản hệ thống" });
            list.Add(new Objects() { Name = "Tài khoản SQL Server" });
            var lup = lookUpEditAuthen;
            lup.Properties.DataSource = list;
            lup.Properties.DisplayMember = "Name";
            lup.Properties.ValueMember = "Name";

            DevExpress.XtraEditors.Controls.LookUpColumnInfoCollection coll = lup.Properties.Columns;
            coll.Add(new DevExpress.XtraEditors.Controls.LookUpColumnInfo("Name", 0, "Name"));

            lup.Properties.DropDownRows = list.Count <= 7 ? 7 : 20;
            lup.Properties.BestFitMode = BestFitMode.BestFitResizePopup;
            lup.Properties.SearchMode = SearchMode.AutoComplete;
            lup.Properties.AutoSearchColumnIndex = 1;
            lup.Properties.ShowHeader = false;
            lup.Properties.Buttons.Clear();
            lup.Properties.Buttons.AddRange(new DevExpress.XtraEditors.Controls.EditorButton[] {
            new DevExpress.XtraEditors.Controls.EditorButton(DevExpress.XtraEditors.Controls.ButtonPredefines.Combo),
            new DevExpress.XtraEditors.Controls.EditorButton(DevExpress.XtraEditors.Controls.ButtonPredefines.Close)});
            lup.ButtonClick += lup_ButtonClick;
        }

        void lup_ButtonClick(object sender, ButtonPressedEventArgs e)
        {
            if (e.Button.Kind == ButtonPredefines.Close)
                ((LookUpEdit)sender).EditValue = null;
        }

        private void lookUpEditServer_EditValueChanged(object sender, EventArgs e)
        {

        }

        private void lookUpEditAuthen_EditValueChanged(object sender, EventArgs e)
        {
            if (!isload)
            {
                if (lookUpEditAuthen.ItemIndex == 0)
                {
                    textEditUsername.Text = "";
                    textEditPassword.Text = "";
                    textEditUsername.Enabled = false;
                    textEditPassword.Enabled = false;
                }
                else
                {
                    textEditUsername.Enabled = true;
                    textEditPassword.Enabled = true;
                }
            }
        }

        bool TestConnect()
        {
            lookUpEditDatabase.Properties.DataSource = null;
            SqlConnection conn;

            //Quyền Windows
            if (lookUpEditAuthen.ItemIndex == 0)
                conn = new SqlConnection("Data Source=" + lookUpEditServer.Text + ";Initial Catalog=master;Integrated Security=True;");
            else
                conn = new SqlConnection("Data Source=" + lookUpEditServer.Text + ";Initial Catalog=master;User Id=" + textEditUsername.Text + ";Password=" + textEditPassword.Text + ";");
            //
            //Lấy database
            //


            try
            {
                conn.Open();

                DataTable databases = conn.GetSchema("Databases");
                List<Objects> list = new List<Objects>();
                foreach (DataRow database in databases.Rows)
                {
                    list.Add(new Objects() { Name = database.Field<String>("database_name") }); 
                }

                var lup = lookUpEditDatabase;
                lup.Properties.DataSource = list;
                lup.Properties.DisplayMember = "Name";
                lup.Properties.ValueMember = "Name";
                DevExpress.XtraEditors.Controls.LookUpColumnInfoCollection coll = lup.Properties.Columns;
                coll.Add(new DevExpress.XtraEditors.Controls.LookUpColumnInfo("Name", 0, "Name"));

                lup.Enabled = true;
                return true;
            }
            catch
            {
                //Program.Message_Error("Lỗi, vui lòng thử lại!");
                return false;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();

                conn.Dispose();
            }

        }

        private void simpleButton1_Click(object sender, EventArgs e)
        {
            if (!TestConnect())
                FormGlobals.Message_Error("Có lỗi xảy ra, vui lòng thử lại", "Lỗi xảy ra");//Error_please_try_again
        }

        private void btnSave_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            if (!TestConnect())
            {
                FormGlobals.Message_Error("Kết nối không thành công", "Lỗi xảy ra");//Error_please_try_again
                return;
            }
            if (lookUpEditDatabase.Text == "")
            {
                FormGlobals.Message_Warning("Bạn chưa chọn cơ sở dữ liệu, vui lòng thử lại", "Cảnh báo");//You_do_not_select_databas_please_try_againe
                return;
            }


            string connectionStringParameters = string.Format("{0};{1};{2};{3}", lookUpEditServer.Text, lookUpEditDatabase.Text, textEditUsername.Text, textEditPassword.Text);

            try
            {
                using (XmlTextWriter tw = new XmlTextWriter(serverParameters, System.Text.Encoding.UTF8))
                {
                    tw.WriteElementString("Parameters", connectionStringParameters);
                }
            }
            catch { }

            string connectionString = String.Format("data source={0};Initial Catalog={1};integrated security=SSPI", lookUpEditServer.Text, lookUpEditDatabase.Text);
            if (lookUpEditAuthen.ItemIndex == 1)
                connectionString = String.Format("data source={0};Initial Catalog={1};user id={2};password={3}", lookUpEditServer.Text, lookUpEditDatabase.Text, textEditUsername.Text, textEditPassword.Text);

            Globals.connectionString = connectionString;

            if (!Properties.Settings.Default.ServerLog.ToString().Contains(lookUpEditServer.Text))
                Properties.Settings.Default.ServerLog += "," + lookUpEditServer.Text;
            Properties.Settings.Default.Save();

            this.DialogResult = DialogResult.OK;
            this.Close();
        }

        private void frmConnection_Activated(object sender, EventArgs e)
        {
            isload = false;
        }

        private void simpleButton2_Click(object sender, EventArgs e)
        {
            BackgroundWorker bgJ = new BackgroundWorker();
            bgJ.DoWork += new DoWorkEventHandler(bgJ_DoWork);
            bgJ.RunWorkerCompleted += new RunWorkerCompletedEventHandler(bgJ_RunWorkerCompleted);
            bgJ.RunWorkerAsync();

            FormGlobals.ShowWaitForm();
        }
        void bgJ_DoWork(object sender, DoWorkEventArgs e)
        {
            try
            {
                loadServer();

                e.Result = true;
            }
            catch (Exception)
            {
                e.Result = false;
            }

        }

        void bgJ_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            lookUpEditServer.Properties.DataSource = loadServer();
            FormGlobals.CloseWaitForm();
        }

        List<Objects> loadServer()
        {
            SqlDataSourceEnumerator servers = SqlDataSourceEnumerator.Instance;
            DataTable serversTable = new DataTable();
            serversTable = servers.GetDataSources();

            List<Objects> list = new List<Objects>();

            if (serversTable.Rows.Count > 0) //chỉ xuất hiện khi máy không kết nối mạng
            {
                string serverName = "";
                string instanceName = "";
                foreach (DataRow row in serversTable.Rows)
                {
                    // Add this to your list
                    serverName = row["ServerName"].ToString();
                    instanceName = row["InstanceName"].ToString();

                    if (instanceName.Trim().Length > 0)
                        list.Add(new Objects() { Name = string.Format("{0}\\{1}", serverName, instanceName) });
                    else
                        list.Add(new Objects() { Name = string.Format("{0}", serverName) });

                }
            }
            else //Khi máy không kết nối mạng thì chỉ lấy ra trên máy này
            {
                RegistryKey localMachine;
                try
                {
                    localMachine = RegistryKey.OpenBaseKey(RegistryHive.LocalMachine, RegistryView.Registry64);
                }
                catch
                {
                    localMachine = RegistryKey.OpenBaseKey(RegistryHive.LocalMachine, RegistryView.Registry32);
                }
                var rk = localMachine.OpenSubKey("SOFTWARE\\Microsoft\\Microsoft SQL Server");
                var instances = (String[])rk.GetValue("InstalledInstances");

                if (instances.Length > 0)
                {
                    string serverName = "";
                    string instanceName = "";

                    foreach (String element in instances)
                    {
                        // Add this to your list
                        serverName = System.Environment.MachineName;
                        instanceName = element;
                        if (instanceName == "MSSQLSERVER")
                            list.Add(new Objects() { Name = string.Format("{0}", serverName) });
                        else
                            list.Add(new Objects() { Name = string.Format("{0}\\{1}", serverName, instanceName) });

                    }

                }

            }
            return list;
        }

    }
}